package com.xiaofangjie.excel;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.bioroadlab.capture.StringUtil;

public class GeneCapture {
	private static String nextLink ="";
	private static List<String> listUrl = null;
	
	private static String s0 = "";
	private static String s_0 = "";
	private static String s1 = "";
	private static String s2 = "";
	private static String s3 = "";
	private static String s4 = "";
	private static String s5 = "";
	private static String s6 = "";
	private static File file = new File("C:\\Users\\Administrator\\Desktop\\小芳姐的excel\\抓取基因数据的excel.xls");
	public static void main(String[] args) {
		if(!file.exists()){
			try {
				HSSFWorkbook workBook1 = new HSSFWorkbook();
				HSSFSheet sheet1 = workBook1.createSheet("基因数据记录");
				HSSFRow titleRow1 = sheet1.createRow(0);
				HSSFCell[] cellArray = new HSSFCell[7];
				for (int i = 0; i < cellArray.length; i++) {
					cellArray[i] = titleRow1.createCell(i);
				}
				cellArray[0].setCellValue("原有OMIM号");
				cellArray[1].setCellValue("基因全名");
				cellArray[2].setCellValue("official symbol");
				cellArray[3].setCellValue("other Aliases");
				cellArray[4].setCellValue("other Designation");
				cellArray[5].setCellValue("location");
				cellArray[6].setCellValue("mim号");
				FileOutputStream out = new FileOutputStream(file, true);
				workBook1.write(out);
				out.flush();
				out.close();
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		
		
		String strLink = lastNextLink();
		System.out.println(strLink+"链接");
		if(strLink.trim().equals("")){
			readPage("http://www.omim.org/search?index=entry&search=prefix:*%20AND%20chromosome_group:A&sort=number%20asc&start=1&limit=10");
		}else {
			readPage(strLink);
		}
	}

	private static void readPage(String strLink) {
		long startTime = new Date().getTime();
		Connection conn = null;
		Statement stat = null;
		listUrl = new ArrayList<String>();
		
		try {
			URL urlGene = new URL(strLink);
			HttpURLConnection httpConn = (HttpURLConnection) urlGene.openConnection();
			InputStreamReader inputStreamReader = new InputStreamReader(httpConn.getInputStream());
			BufferedReader bufferedReader = new BufferedReader(inputStreamReader);
			String string = "";
			while((string=bufferedReader.readLine())!=null){
				if (!(string.trim().equals(""))) {
					if (string.contains("<a href=\"/entry/")) {
						string = "http://www.omim.org" + string.substring(string.indexOf("\"/") + 1, string.indexOf("\">"));
						string = string.trim();
						string = string.replaceAll(" ", "%20");//疾病链接
						List<String> list = findTenGene();//十个疾病链接
						if(list.size()>0){
							if (!list.contains(string)) {
								System.out.println("------------------------");
								getInGeneLinkGetData(string);
								Class.forName("com.mysql.jdbc.Driver");
								conn = (Connection) DriverManager.getConnection("jdbc:mysql:///z_capturegene","root", "root");
								stat = (Statement) conn.createStatement();
								if("".equals(nextLink)){
									nextLink = "http://www.omim.org/search?index=entry&search=prefix:*%20AND%20chromosome_group:A&sort=number%20asc&start=1&limit=10";
								}
								String sql = "INSERT INTO t_gene(nextLink,diseaseLink) VALUE('" + nextLink + "','" + string + "')";
								stat.execute(sql);
							} else{
								System.out.println("在抓取已经有的");
							}
						}else {
							getInGeneLinkGetData(string);
							Class.forName("com.mysql.jdbc.Driver");
							conn = (Connection) DriverManager.getConnection("jdbc:mysql:///z_capturegene","root", "root");
							stat = (Statement) conn.createStatement();
							if("".equals(nextLink)||nextLink==null){
								nextLink = "http://www.omim.org/search?index=entry&search=prefix:*%20AND%20chromosome_group:A&sort=number%20asc&start=1&limit=10";
							}
							String sql = "INSERT INTO t_gene(nextLink,diseaseLink) VALUE('" + nextLink + "','" + string + "')";
							stat.execute(sql);
						}
					} else if (string.toString().contains("\">Next")) {
						string = "http://www.omim.org" + string.substring(string.indexOf("\"/") + 1, string.indexOf(">Next") - 1);
						string = string.replaceAll(" ", "%20");// 下页的链接
						if (listUrl.size()>0) {
							if (listUrl.contains(string.toString())) {
								listUrl.add(string);
								nextLink = string;
								readPage(string);
							} else {
								listUrl.add(string);
								nextLink = string;
							}
						}else {
							listUrl.add(string);
							nextLink = string;
						}
						nextLink = string;
								
						listUrl.add(string);
						
					}
				}
			}
			System.out.println("一个用时："+((new Date().getTime())-startTime));
		} catch (MalformedURLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private static String lastNextLink(){
		Connection conn = null;
		Statement stat = null;
		String nextLink = "";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = (Connection) DriverManager.getConnection("jdbc:mysql:///z_capturegene", "root","root");
			stat = (Statement) conn.createStatement();
			ResultSet rs1 = stat.executeQuery("select * from t_gene order by id DESC limit 1");
			while(rs1.next()){
				nextLink = rs1.getString("nextLink");
				
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return nextLink;
	}
	
	private static List<String> findTenGene() {
		Connection conn = null;
		Statement stat = null;
		List<String> list = new ArrayList<String>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = (Connection) DriverManager.getConnection("jdbc:mysql:///z_capturegene", "root","root");
			stat = (Statement) conn.createStatement();
			ResultSet rs1 = stat.executeQuery("select * from t_gene order by id DESC limit 10");
			while(rs1.next()){
				String diseaseLink = rs1.getString("diseaseLink");
				list.add(diseaseLink);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public static void getInGeneLinkGetData(String geneUrl){
		try {
			
			URL url = new URL(geneUrl);
			HttpURLConnection conn = ((HttpURLConnection) url.openConnection());
			InputStreamReader input = new InputStreamReader(conn.getInputStream());
			BufferedReader buff = new BufferedReader(input);
			String str = "";
			while((str=buff.readLine())!=null){
				if(str.contains("<span class=\"mim-number")){
					str = buff.readLine();
					str = StringUtil.getSupString(str);
					s0 = str.trim();
				}else if(str.contains("HGNC Approved Gene Symbol:")){
					str = str.substring(str.lastIndexOf("\">")+2, str.length());
					str = StringUtil.getSupString(str.trim());
					s_0=str;
				}
			}
			if(!s0.equals("")&&!s_0.equals("")){
				
				captureGeneData(s0,s_0);
			}
		} catch (MalformedURLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private static void captureGeneData(String s_0,String strParameter) {
		try {
			/*URL urlGene = new URL(strLink);
			HttpURLConnection httpConn = (HttpURLConnection) urlGene.openConnection();
			InputStreamReader inputStreamReader = new InputStreamReader(httpConn.getInputStream());
			BufferedReader bufferedReader = new BufferedReader(inputStreamReader);*/
			URL url = new URL("http://www.ncbi.nlm.nih.gov/gene?term=" + strParameter);
			System.out.println("独一无二的：→"+url.getAuthority());
			HttpURLConnection conn = ((HttpURLConnection) url.openConnection());
			InputStreamReader input = new InputStreamReader(conn.getInputStream());
			BufferedReader buff = new BufferedReader(input);
			String str = "";
			while((str=buff.readLine())!=null){
				if(str.contains("[Homo sapiens]")){
					if(str.contains("Official Symbol:")){
						String fullName = str.substring(str.indexOf("[Homo sapiens]") + "[Homo sapiens]".length(), str.indexOf("Official Symbol:"));
						s1 = StringUtil.getSupString(fullName);
						str = buff.readLine();
						str = StringUtil.getSupString(str);//第二行的数据
						//String officialSymbol = str.substring(0, str.indexOf("Other Aliases"));
						if(str.contains("Other Aliases:")){
							String os = str.substring(0, str.indexOf("Other Aliases"));
							if(str.contains("Other Designations:")){
								String oA = str.substring(str.indexOf("Other Aliases:")+"Other Aliases:".length(), str.indexOf("Other Designations:"));
								s3 = oA;
								if(str.contains("Location:")){
									String oD = str.substring(str.indexOf("Designations:")+"Designations:".length(), str.indexOf("Location:"));
									s4 = oD;
									if(str.contains("MIM:")){
										String location = str.substring(str.indexOf("Location:")+"Location:".length(), str.indexOf("MIM:"));
										s5 = location;
										if(str.contains("ID:")){
											
											String mim = str.substring(str.indexOf("MIM:")+"MIM:".length(), str.indexOf("ID:"));
											s6 = mim;
										}else if(str.contains("Order cDNA clone")){
											String mim = str.substring(str.indexOf("MIM:")+"MIM:".length(), str.indexOf("Order cDNA clone"));
											s6 = mim;
										}
									}
								}
							}else if(str.contains("Location:")){
								String oD = str.substring(str.indexOf("Designations:")+"Designations:".length(), str.indexOf("Location:"));
								s4 = oD;
								if(str.contains("MIM:")){
									String location = str.substring(str.indexOf("Location:")+"Location:".length(), str.indexOf("MIM:"));
									s5 = location;
									if(str.contains("ID:")){
										String mim = str.substring(str.indexOf("MIM:")+"MIM:".length(), str.indexOf("ID:"));
										s6 = mim;
									}else if(str.contains("Order cDNA clone")){
										String mim = str.substring(str.indexOf("MIM:")+"MIM:".length(), str.indexOf("Order cDNA clone"));
										s6 = mim;
									}
								}
							}else if(str.contains("MIM:")){
								String location = str.substring(str.indexOf("Location:")+"Location:".length(), str.indexOf("MIM:"));
								s5 = location;
								if(str.contains("ID:")){
									String mim = str.substring(str.indexOf("MIM:")+"MIM:".length(), str.indexOf("ID:"));
									s6 = mim;
								}else if(str.contains("Order cDNA clone")){
									String mim = str.substring(str.indexOf("MIM:")+"MIM:".length(), str.indexOf("Order cDNA clone"));
									s6 = mim;
								}
							}
							s2 = os;
						}else if(str.contains("Other Designations:")){
							
							String os = str.substring(0, str.indexOf("Other Designations:"));
							s2 = os;
							String oA = "";
							s3 = oA;
							if(str.contains("Location:")){
								String oD = str.substring(str.indexOf("Designations:")+"Designations:".length(), str.indexOf("Location:"));
								s4 = oD;
								if(str.contains("MIM:")){
									String location = str.substring(str.indexOf("Location:")+"Location:".length(), str.indexOf("MIM:"));
									s5 = location;
									if(str.contains("ID:")){
										String mim = str.substring(str.indexOf("MIM:")+"MIM:".length(), str.indexOf("ID:"));
										s6 = mim;
									}else if(str.contains("Order cDNA clone")){
										String mim = str.substring(str.indexOf("MIM:")+"MIM:".length(), str.indexOf("Order cDNA clone"));
										s6 = mim;
									}
								}
							}
						} else if(str.contains("Location:")){
							String oS = str.substring(0, str.indexOf("Location:"));
							s2 = oS;
							String oA ="";
							String oD = "";
							s3 = oA;
							s4 = oD;
							if(str.contains("MIM:")){
								String location = str.substring(str.indexOf("Location:")+"Location:".length(), str.indexOf("MIM:"));
								s5 = location;
								if(str.contains("ID:")){
									String mim = str.substring(str.indexOf("MIM:")+"MIM:".length(), str.indexOf("ID:"));
									s6 = mim;
								}else if(str.contains("Order cDNA clone")){
									String mim = str.substring(str.indexOf("MIM:")+"MIM:".length(), str.indexOf("Order cDNA clone"));
									s6 = mim;
								}
							}
						}else if(str.contains("MIM:")){
							String oS = str.substring(0, str.indexOf("MIM:"));
							s2 = oS;
							String oA ="";
							String oD = "";
							String location = "";
							s3 = oA;
							s4 = oD;
							s5 = location;
							if(str.contains("ID:")){
								String mim = str.substring(str.indexOf("MIM:")+"MIM:".length(), str.indexOf("ID:"));
								s6 = mim;
							}else if(str.contains("Order cDNA clone")){
								String mim = str.substring(str.indexOf("MIM:")+"MIM:".length(), str.indexOf("Order cDNA clone"));
								s6 = mim;
							}
						}
					}
					//存excel表格
					System.out.println("s_0的值："+s_0);
					System.out.println("s1的值："+s1);
					System.out.println("s2的值："+s2);
					System.out.println("s3的值："+s3);
					System.out.println("s4的值："+s4);
					System.out.println("s5的值："+s5);
					System.out.println("s6的值："+s6);
					HSSFWorkbook workBook = new HSSFWorkbook(new FileInputStream(file));
					HSSFSheet sheet = workBook.getSheet("基因数据记录");
					HSSFRow titleRow = sheet.getRow(sheet.getLastRowNum());
					
					titleRow = sheet.createRow(sheet.getLastRowNum() + 1);
					
					titleRow.createCell(0).setCellValue(s_0.trim().toString());
					titleRow.createCell(1).setCellValue(s1.trim().toString());
					titleRow.createCell(2).setCellValue(s2.trim().toString());
					titleRow.createCell(3).setCellValue(s3.trim().toString());
					titleRow.createCell(4).setCellValue(s4.trim().toString());
					titleRow.createCell(5).setCellValue(s5.trim().toString());
					titleRow.createCell(6).setCellValue(s6.trim().toString());
					
					FileOutputStream out = new FileOutputStream(file);
					out.flush();
					workBook.write(out);
					out.close();
				}
			}
			buff.close();
			input.close();
		} catch (MalformedURLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

}
